package me.zhengjie.gas.service;

import me.zhengjie.dto.DeptDto;
import me.zhengjie.dto.UserDto;
import me.zhengjie.gas.model.ResponseDto;
import me.zhengjie.gas.utils.CamelCaseRowMapper;
import me.zhengjie.service.DeptService;
import me.zhengjie.service.UserService;
import me.zhengjie.utils.SecurityUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Service;

import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

@Service
public class ReportService {


	@Autowired
	private NamedParameterJdbcTemplate jdbcTemplate;

	@Autowired
	private UserService userService;

	@Autowired
	private DeptService deptService;

	private List<Long> getChildrenDeptIdByUserId(Long userid) {
		UserDto user = userService.findById(SecurityUtils.getCurrentUserId());
		Long deptId = user.getDept().getId();

		DeptDto deptDto = deptService.findById(deptId);
		List<Long> children;
		if (deptDto.getChildren() == null) {
			children = new ArrayList<>();
		} else {
			children = deptDto.getChildren().stream().map(DeptDto::getId).collect(Collectors.toList());
		}
		children.add(deptId);
		return children;
	}

	public ResponseDto saleDetailsByTimeRange(Long deptId, LocalDateTime startDate, LocalDateTime endDate) {
		String sql = "select \n" +
				"\td.*, \n" +
				"\tcoalesce(td.amount, 0) as amount, \n" +
				"\tcoalesce(td.volume, 0) as volume\n" +
				"from (\n" +
				"\tselect\n" +
				"\t\textract(year from date_series.date) as \"year\", \n" +
				"\t\textract(month from date_series.date) as \"month\",\n" +
				"\t\textract(day from date_series.date) as \"day\"\n" +
				"\tfrom\n" +
				"\t\tgenerate_series(:startDate, :endDate, '1 day'::interval) as date_series(date)\n" +
				") d\n" +
				"left join (\n" +
				"\tselect\n" +
				"\t\td.\"year\", d.\"month\", d.\"day\", sum(d.amount) as amount, sum(d.volume) as volume \n" +
				"\tfrom (\n" +
				"\t\tselect\n" +
				"\t\t\textract(year from td.transaction_time) as \"year\",\n" +
				"\t\t\textract(month from td.transaction_time) as \"month\",\n" +
				"\t\t\textract(day from td.transaction_time) as \"day\",\n" +
				"\t\t\t*\n" +
				"\t\tfrom\n" +
				"\t\t\t{schema}transaction_data td\n" +
				"\t\twhere td.transaction_time >= :startDate and td.transaction_time <= :endDate and td.dept_id in (:deptIds)\n" +
				"\t)d\n" +
				"\tgroup by d.\"year\", d.\"month\", d.\"day\"\n" +
				"\torder by d.\"year\", d.\"month\", d.\"day\"\n" +
				") td on td.\"year\" = d.\"year\" and td.\"month\" = d.\"month\" and td.\"day\" = d.\"day\"";

		MapSqlParameterSource params = new MapSqlParameterSource();
		params.addValue("startDate", startDate);
		params.addValue("endDate", endDate);
		params.addValue("deptIds", getChildrenDeptIdByUserId(SecurityUtils.getCurrentUserId()));
		sql = sql.replace("{schema}", "eladmin.");
		List<Map<String, Object>> list = jdbcTemplate.queryForList(sql, params);
		return new ResponseDto(0, "查询成功", list);
	}

	public ResponseDto transactionByDateTimeRange(Long deptId, LocalDateTime startDate, LocalDateTime endDate) {
		String sql = "select td.id as \"id\", td.amount as \"amount\", td.volume as \"volume\", td.price as \"price\", \n" +
				"td.transaction_time, td.oil_name, d.\"name\", td.line_id from {schema}transaction_data td " +
				"left join {schema}sys_dept d on d.dept_id = :deptId " +
				"where td.dept_id in (:deptIds) and td.transaction_time >= :startDate and td.transaction_time <= :endDate\n" +
				"order by td.transaction_time desc limit 100";
		MapSqlParameterSource params = new MapSqlParameterSource();
		params.addValue("deptId", deptId);
		params.addValue("deptIds", getChildrenDeptIdByUserId(SecurityUtils.getCurrentUserId()));
		params.addValue("startDate", startDate);
		params.addValue("endDate", endDate);
		sql = sql.replace("{schema}", "eladmin.");
		List<Map<String, Object>> mapList = jdbcTemplate.query(sql, params, new CamelCaseRowMapper());

		return new ResponseDto(0, "success", mapList);
	}

	public ResponseDto oilSaleDetails(Long deptId, LocalDateTime startDate, LocalDateTime endDate) {
		String sql = "select \n" +
				"\ttd.oil_id,\n" +
				"\ttd.oil_name,\n" +
				"\ttd.color,\n" +
				"\tsum(td.amount) as \"amount\",\n" +
				"\tsum(td.volume) as \"volume\"\n" +
				"from\n" +
				"\t{schema}transaction_data td\n" +
				"left join {schema}sys_dept d on\n" +
				"\td.dept_id = td.dept_id\n" +
				"where\n" +
				"\ttd.dept_id in (:deptIds)\n" +
				"\tand td.transaction_time >= :startDate\n" +
				"\tand td.transaction_time <= :endDate\n" +
				"group by\n" +
				"\ttd.oil_id, td.oil_name, td.color";

		MapSqlParameterSource params = new MapSqlParameterSource();
		params.addValue("deptId", deptId);
		params.addValue("deptIds", getChildrenDeptIdByUserId(SecurityUtils.getCurrentUserId()));
		params.addValue("startDate", startDate);
		params.addValue("endDate", endDate);

		sql = sql.replace("{schema}", "eladmin.");
		List<Map<String, Object>> mapList = jdbcTemplate.query(sql, params, new CamelCaseRowMapper());
		return new ResponseDto(0, "success", mapList);
	}

	public ResponseDto allDeptSaleDetails(Long deptId, LocalDateTime startDate, LocalDateTime endDate) {
		String sql = "select \n" +
				"\td.dept_id, d.name,  e.volume, e.amount \n" +
				"from {schema}sys_dept d\n" +
				"left join (\n" +
				"\tselect \n" +
				"\t\ttd.dept_id, sum(td.volume) as \"volume\", sum(td.amount) as \"amount\" \n" +
				"\tfrom {schema}transaction_data td\n" +
				"\twhere td.dept_id in (:deptIds) and td.transaction_time >= :startDate and td.transaction_time <= :endDate\n" +
				"\tgroup by td.dept_id\n" +
				") e on d.dept_id = e.dept_id\n" +
				"where d.dept_id in (:deptIds)\n" +
				"order by e.amount desc NULLS LAST";

		MapSqlParameterSource params = new MapSqlParameterSource();
		params.addValue("deptIds", getChildrenDeptIdByUserId(SecurityUtils.getCurrentUserId()));
		params.addValue("startDate", startDate);
		params.addValue("endDate", endDate);

		sql = sql.replace("{schema}", "eladmin.");
		List<Map<String, Object>> mapList = jdbcTemplate.query(sql, params, new CamelCaseRowMapper());
		return new ResponseDto(0, "success", mapList);
	}

	public ResponseDto lastYearSalesDetails(Long deptId) {
		String sql = "with \n" +
				"t_month as (\n" +
				"\tSELECT \n" +
				"\t    EXTRACT(YEAR FROM date_trunc('month', CURRENT_DATE) - INTERVAL '1 month' * i) AS year,\n" +
				"\t    EXTRACT(MONTH FROM date_trunc('month', CURRENT_DATE) - INTERVAL '1 month' * i) AS month\n" +
				"\tFROM \n" +
				"\t    generate_series(0, 11) AS s(i)\n" +
				"),\n" +
				"t_trans as (\n" +
				"\tselect \n" +
				"\t\tEXTRACT(YEAR FROM date_trunc('month', td.transaction_time)) AS year,\n" +
				"\t    EXTRACT(MONTH FROM date_trunc('month', td.transaction_time)) AS month,\n" +
				"\t\t* \n" +
				"\tfrom {schema}transaction_data td \n" +
				")\n" +
				"select tm.year, tm.month, td.amount, td.volume from t_month tm\n" +
				"left join (\n" +
				"\tselect \n" +
				"\t\tyear, \n" +
				"\t\tmonth, \n" +
				"\t\tsum(amount) as amount, \n" +
				"\t\tsum(volume) as volume \n" +
				"\tfrom t_trans \n" +
				"\twhere dept_id in (:deptIds)\n" +
				"\tgroup by year, month\n" +
				")td on td.year=tm.year and td.month=tm.month\n" +
				"ORDER BY year DESC, month DESC\n";

		MapSqlParameterSource params = new MapSqlParameterSource();
		params.addValue("deptIds", getChildrenDeptIdByUserId(SecurityUtils.getCurrentUserId()));

		sql = sql.replace("{schema}", "eladmin.");
		List<Map<String, Object>> mapList = jdbcTemplate.query(sql, params, new CamelCaseRowMapper());
		return new ResponseDto(0, "success", mapList);
	}

	public ResponseDto todaySalesDetails(Long deptId) {
		String sql = "select sum(amount)as amount, sum(volume) as volume  from {schema}transaction_data td \n" +
				"where \n" +
				"td.dept_id in (:deptIds)\n" +
				"and td.transaction_time >= date_trunc('day', CURRENT_DATE) \n" +
				"and td.transaction_time <= (date_trunc('day', CURRENT_DATE) + INTERVAL '1 day' - INTERVAL '1 second')";

		MapSqlParameterSource params = new MapSqlParameterSource();
		params.addValue("deptIds", getChildrenDeptIdByUserId(SecurityUtils.getCurrentUserId()));

		sql = sql.replace("{schema}", "eladmin.");
		List<Map<String, Object>> mapList = jdbcTemplate.query(sql, params, new CamelCaseRowMapper());
		return new ResponseDto(0, "success", mapList);
	}

	public ResponseDto monthSalesDetails(Long deptId) {
		String sql = "select sum(amount)as amount, sum(volume) as volume  from {schema}transaction_data td \n" +
				"where \n" +
				"td.dept_id in (:deptIds)\n" +
				"and td.transaction_time >= date_trunc('month', CURRENT_DATE) \n" +
				"and td.transaction_time <= (date_trunc('month', CURRENT_DATE) + INTERVAL '1 month' - INTERVAL '1 second')";

		MapSqlParameterSource params = new MapSqlParameterSource();
		params.addValue("deptIds", getChildrenDeptIdByUserId(SecurityUtils.getCurrentUserId()));

		sql = sql.replace("{schema}", "eladmin.");
		List<Map<String, Object>> mapList = jdbcTemplate.query(sql, params, new CamelCaseRowMapper());
		return new ResponseDto(0, "success", mapList);
	}
}
